Overview:
- DROP TABLE SQL statement drops an existing table from the SQLite database.
- When the IF EXISTS clause is used along with DROP TABLE syntax SQLite will not report any error message if the table does not exist.
- DROP TABLE will delete any associated indices, triggers.
Dropping an SQLite Table from a Python Program:
- The Sqlite3 Python module provides the interface for Database Programming with SQLite from Python Code.
- A database connection to the SQLite can be obtained by calling connect() method with the database file name as the argument.
- A cursor object is returned by calling the cursor() method of the database connection object.
- Any SQL statement supported by SQLite can be executed through the cursor object.
- The DROP TABLE statement and the DROP TABLE statement with an IF EXISTS clause can be executed through the cursor object.
Example 1 – Simple DROP TABLE Example:
|
# --------- Example Python Program for dropping an SQLite Table---------
# import the sqlite3 module import sqlite3
# Connect to the demo database connection = sqlite3.connect("demo.db")
# Get a cursor object cursor = connection.cursor()
# Execute the DROP Table SQL statement dropTableStatement = "DROP TABLE demotable" cursor.execute(dropTableStatement)
# Close the connection object connection.close() |
Output 1 - When there is no table with the specified name exists:
|
Traceback (most recent call last): File "drop_sqlite1.py", line 14, in <module> cursor.execute(dropTableStatement) sqlite3.OperationalError: no such table: demotable |
Output 2- When the table existed and got deleted:
The output below does not include demotable as it was deleted.
|
[('table', 'test', 'test', 2, 'CREATE TABLE test(id int)')] |
Output 3 - When IF EXISTS clause is added:
|
dropTableStatement = "DROP TABLE demotable" |
When the example Python Program is modified to have the DROP TABLE statement as given below to include an IF EXISTS clause SQLite does not throw any Error and the output will be the same as output 2.
Example 2 – SQLite DROP TABLE with foreign key constraints:
DROP TABLE command in turn deletes the rows present in the table, using DELETE FROM command. However when such an implicit DELETE FROM is initiated if it violates any foreign key constraints SQLite will throw an error and will not delete the rows and DROP TABLE will fail, forcing the integrity of the database.
Assume there are two tables named
1) Teacher
2) Course
in an SQLite Database and Course table is defined with a foreign key definition to the Teacher Table.
|
CREATE TABLE Teacher( TeacherId INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT );
CREATE TABLE Course( CourseId INTEGER, CourseName TEXT, Teacher INTEGER, FOREIGN KEY(Teacher) REFERENCES Teacher(TeacherId) ); |
The DROP TABLE will fail if deleting any Teacher record renders a course invalid.
|
# --------- Example Python Program for dropping an SQLite Table---------
# import the sqlite3 module import sqlite3
# Connect to the demo database connection = sqlite3.connect("demo.db")
# Get a cursor object cursor = connection.cursor()
# Enable Foreign key constraints cursor.execute("PRAGMA foreign_keys = ON;")
# Execute the DROP Table SQL statement dropTableStatement = "DROP TABLE Teacher" cursor.execute(dropTableStatement)
# Close the connection object connection.close()
|
Output:
|
Traceback (most recent call last): File "drop_sqlite2.py", line 17, in <module> cursor.execute(dropTableStatement) sqlite3.IntegrityError: FOREIGN KEY constraint failed |